﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;

namespace LdExpress
{
    public class BorrowBook
    {
        public long ID = -1;
        public DateTime CreateDate = DateTime.Now;
        public string FileNumber = "";
        public long IdProvince = 0;
        public string PaymentSubject = "";
        public string Description = "";
        public string BankName = "";
        public double ReciprocalAccount_Had = 0;
        public double ReciprocalAccount_Debt = 0;
        public double Interest = 0;
        public double Amount_Debt = 0;
        public double Amount_Paid = 0;
        public double Amount_Owed = 0;
        public DateTime Date_Debt = DateTime.Now;
        public long Duration = 0;
        public int CloseFile = 0;

        public BorrowBook()
        {
        }

        public BorrowBook(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_BorrowBook";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "Insert into " + strTableName + " (" +
                                "CreateDate," +
                                "Date_Debt," +
                                "FileNumber," +
                                "PaymentSubject," +
                                "Description," +
                                "BankName," +
                                "ReciprocalAccount_Had," +
                                "ReciprocalAccount_Debt," +
                                "Amount_Debt," +
                                "Amount_Paid," +
                                "Amount_Owed," +
                                "Interest," +
                                "Duration," +
                                "IdProvince," +
                                "CloseFile" +
                             ") " +
                         " values(" +
                              DBModule.RefineDatetime(CreateDate) +
                             "," + DBModule.RefineDatetime(Date_Debt) +
                             ",'" + DBModule.RefineString(FileNumber) + "'" +
                             "," + "'" + DBModule.RefineString(PaymentSubject) + "'" +
                             "," + "'" + DBModule.RefineString(Description) + "'" +
                             "," + "'" + DBModule.RefineString(BankName) + "'" +
                             "," + ReciprocalAccount_Had.ToString() +
                             "," + ReciprocalAccount_Debt.ToString() +
                             "," + Amount_Debt.ToString() +
                             "," + Amount_Paid.ToString() +
                             "," + Amount_Owed.ToString() +
                             "," + Interest.ToString() +
                             "," + Duration.ToString() +
                             "," + IdProvince.ToString() +
                             "," + CloseFile.ToString() +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("BorrowBook", "add", cn, trans);
                //GV.Response.Write(strSQL);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                        "CreateDate = " + DBModule.RefineDatetime(CreateDate) + "," +
                        "Date_Debt = " + DBModule.RefineDatetime(Date_Debt) + "," +
                        "FileNumber =  '" + FileNumber.ToString() + "'," +
                        "PaymentSubject = '" + DBModule.RefineString(PaymentSubject) + "'," +
                        "Description = '" + DBModule.RefineString(Description) + "'," +
                        "BankName = '" + DBModule.RefineString(BankName) + "'," +
                        "ReciprocalAccount_Had = " + ReciprocalAccount_Had + "," +
                        "ReciprocalAccount_Debt = " + ReciprocalAccount_Debt + "," +
                        "Amount_Debt = " + Amount_Debt + "," +
                        "Amount_Paid = " + Amount_Paid + "," +
                        "Amount_Owed = " + Amount_Owed + "," +
                        "Interest = " + Interest + "," +
                        "Duration = " + Duration + "," +
                        "IdProvince =  " + IdProvince.ToString() +
                        ",CloseFile =  " + CloseFile.ToString() +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("BorrowBook", "edit", cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_BorrowBook WHERE ID = " + this.ID.ToString();

            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("BorrowBook", "delete", cn, trans);
        }
        public static void DoCloseFile(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Update tbl_BorrowBook set CloseFile=1";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
        }
        public static void DoOpenFile(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Update tbl_BorrowBook set CloseFile=0";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
        }
        public static int GetCountStatusFile(int StatusFile, MySqlConnection cn, MySqlTransaction trans)
        {
            int retVal = 0;
            string sqlCommand = "Select Count(ID) From  tbl_BorrowBook where CloseFile=" + StatusFile;
            string Status = DBModule.ExecuteQueryForOneResult(sqlCommand, cn, trans);
            if (Status != null) retVal = int.Parse(Status);
            return retVal;
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_BorrowBook WHERE ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("CreateDate")) CreateDate = DateTime.Parse(dr["CreateDate"].ToString());
                if (!dr.IsNull("Date_Debt")) Date_Debt = DateTime.Parse(dr["Date_Debt"].ToString());
                if (!dr.IsNull("FileNumber")) FileNumber = dr["FileNumber"].ToString();
                if (!dr.IsNull("IdProvince")) IdProvince = long.Parse(dr["IdProvince"].ToString());
                if (!dr.IsNull("Interest")) Interest = double.Parse(dr["Interest"].ToString());
                if (!dr.IsNull("BankName")) BankName = dr["BankName"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("ReciprocalAccount_Had")) ReciprocalAccount_Had = double.Parse(dr["ReciprocalAccount_Had"].ToString());
                if (!dr.IsNull("ReciprocalAccount_Debt")) ReciprocalAccount_Debt = double.Parse(dr["ReciprocalAccount_Debt"].ToString());
                if (!dr.IsNull("Amount_Debt")) Amount_Debt = double.Parse(dr["Amount_Debt"].ToString());
                if (!dr.IsNull("Amount_Paid")) Amount_Paid = double.Parse(dr["Amount_Paid"].ToString());
                if (!dr.IsNull("Amount_Owed")) Amount_Owed = double.Parse(dr["Amount_Owed"].ToString());
                if (!dr.IsNull("Duration")) Duration = long.Parse(dr["Duration"].ToString());
                if (!dr.IsNull("IdProvince")) IdProvince = long.Parse(dr["IdProvince"].ToString());
                if (!dr.IsNull("CloseFile")) CloseFile = int.Parse(dr["CloseFile"].ToString());
            }
        }

        public static void GetList(string Condittion, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_BorrowBook where 1=1 ";
            if ((Condittion != "") && (Condittion != null))
            {
                strSQL += Condittion;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_BorrowBook where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
    }
}

